﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class FamiliasDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeFamilia(ByVal pIdFamilia As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM familias " & _
                                            "WHERE IdFamilia = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("IdFamilia", pIdFamilia)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function existeFamiliaCla(ByVal pClaFamilia As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM familias " & _
                                            "WHERE ClaFamilia = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("ClaFamilia", pClaFamilia)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraFamilia(ByVal pFamilia As FamiliasBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO familias(IdEmpresa,ClaFamilia, DesFamilia,Status) " & _
                                            "VALUES (0,@ClaFamilia,@DesFamilia,@Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@ClaFamilia", pFamilia.ClaFamilia)
                    myComm.Parameters.AddWithValue("@DesFamilia", pFamilia.DesFamilia)
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaFamilia(ByVal pFamilia As FamiliasBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM familias " & _
                                            "WHERE IdFamilia <> @id " &
                                                "AND  ClaFamilia = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pFamilia.IdFamilia)
                    myComm.Parameters.AddWithValue("@descripcion", pFamilia.ClaFamilia)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la Clave especificada." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE familias " & _
                                            "SET ClaFamilia = @ClaFamilia, " & _
                                                "DesFamilia = @DesFamilia, " & _
                                                "Status = @Status " & _
                                            "WHERE IdFamilia = @IdFamilia;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@ClaFamilia", pFamilia.ClaFamilia)
                    myComm.Parameters.AddWithValue("@DesFamilia", pFamilia.DesFamilia)
                    Select Case pFamilia.Status
                        Case FamiliasBE.EstatusFamilias.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case FamiliasBE.EstatusFamilias.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "A")
                    End Select
                    myComm.Parameters.AddWithValue("@IdFamilia", pFamilia.IdFamilia)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal pId As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE familias " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdFamilia = @IdFamilia;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdFamilia", pId)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getFamilia(ByVal pIdFamilia As Integer) As FamiliasBE
        Dim tmpfamilia As FamiliasBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdFamilia, ClaFamilia, DesFamilia, Status " & _
                                            "FROM familias " & _
                                            "WHERE IdFamilia = @IdFamilia;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdFamilia", pIdFamilia)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpfamilia = FamiliasFact.Build(myDR)
                        Else
                            tmpfamilia = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpfamilia

        Catch ex As Exception
            Throw
        Finally
            tmpfamilia = Nothing
        End Try
    End Function

    Public Function getlistadoFamilias(ByVal pDesFamilia As String, ByVal pClaFamilia As String,
                                       ByVal pStatus As Enumeraciones.estatusParaBusquedas,
                                       ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                       ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                       ByRef pTotalRegistros As Integer) As List(Of FamiliasBE)
        pTotalRegistros = 0
        Dim FamiliasLs As List(Of FamiliasBE) = New List(Of FamiliasBE)
        Dim qryEstatus As String = ""

        Select Case pStatus
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
            Case Enumeraciones.estatusParaBusquedas.Todos
                qryEstatus = ""
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdEmpresa, IdFamilia, ClaFamilia, DesFamilia, Status " & _
                                           "FROM familias " & _
                                           "WHERE DesFamilia LIKE @descFam " & _
                                           "AND ClaFamilia LIKE @clavFam " & _
                                           qryEstatus &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.FamiliasProductos, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@descFam", "%" & pDesFamilia & "%")
                    myComm.Parameters.AddWithValue("@clavFam", "%" & pClaFamilia & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Familias As FamiliasBE = FamiliasFact.Build(myDR)
                            If (Not Familias Is Nothing) Then
                                FamiliasLs.Add(Familias)
                            End If
                            Familias = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return FamiliasLs
        Catch ex As Exception
            Throw
        Finally
            FamiliasLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function

#End Region

    'Public Function registraFamilias(ByVal pFamilias As FamiliasBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 4) As OdbcParameter

    '    Try

    '        instrSql = "INSERT INTO ""Familias""(""ClaFamilia"", ""DesFamilia"", ""IdEmpresa"", ""IdFamilia"", ""Status"") " & _
    '                                      "VALUES (?,?,?,?,?);"

    '        oInsParm(0) = New OdbcParameter("", pFamilias.ClaFamilia)
    '        oInsParm(1) = New OdbcParameter("", pFamilias.DesFamilia)
    '        oInsParm(2) = New OdbcParameter("", pFamilias.IdEmpresa)
    '        oInsParm(3) = New OdbcParameter("", pFamilias.IdFamilia)
    '        oInsParm(4) = New OdbcParameter("", pFamilias.Status)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                If (myComm.ExecuteNonQuery() > 0) Then
    '                    Return True
    '                Else
    '                    Return False
    '                End If
    '            End Using
    '        End Using
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function modificaFamilias(ByVal pFamilias As FamiliasBE) As Boolean
    '    Dim instrSql As String
    '    Dim oInsParm(0 To 16) As OdbcParameter
    '    Try
    '        instrSql = "UPDATE ""Familias"" " & _
    '                                    "SET ""ClaFamilia""=?, ""DesFamilia""=?, ""IdFamilia""=?, ""Status""=? " & _
    '                                            "WHERE ""IdEmpresa"" = ?;"

    '        oInsParm(0) = New OdbcParameter("", pFamilias.ClaFamilia)
    '        oInsParm(1) = New OdbcParameter("", pFamilias.DesFamilia)
    '        oInsParm(2) = New OdbcParameter("", pFamilias.IdFamilia)
    '        oInsParm(3) = New OdbcParameter("", pFamilias.Status)
    '        oInsParm(4) = New OdbcParameter("", pFamilias.IdEmpresa)

    '        'Crea el objeto connection
    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.AddRange(oInsParm)
    '                myConn.Open()
    '                myComm.ExecuteNonQuery()
    '                Return True
    '            End Using
    '        End Using

    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        instrSql = Nothing
    '        oInsParm = Nothing
    '    End Try
    'End Function

    'Public Function getFamilias(ByVal pIdEmpr As Integer, ByVal pStrFamilias As String) As List(Of FamiliasBE)
    '    Dim FamiliasLs As List(Of FamiliasBE) = New List(Of FamiliasBE)
    '    Dim instrSql As String

    '    Try
    '        instrSql = "SELECT ""ClaFamilia"", ""DesFamilia"", ""IdEmpresa"", ""IdFamilia"", ""Status"" " & _
    '                                 "FROM ""Familias"" " & _
    '                                "WHERE ""IdEmpresa"" = ? " & _
    '                                    "AND ""ClaFamilia"" LIKE '%" & pStrFamilias & "%' ;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        'Dim Familias As FamiliasBE = FamiliasFact.Build(myDR)
    '                        'If (Not Familias Is Nothing) Then
    '                        '    FamiliasLs.Add(Familias)
    '                        'End If
    '                        'Familias = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return FamiliasLs
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        FamiliasLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function
End Class
